package DAO.MySQL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import DAO.OrderDetailDAO;
import Model.Order;
import Model.OrderDetail;
import Model.Product;
import Util.JDBCUtil;

/**
 * @author TONY
 *
 */
public class OrderDetailDAOImpl implements OrderDetailDAO {
	private Connection conn=JDBCUtil.GetConnection();
	
	public OrderDetail getOrderDetailByOrderId(int id)
	{
		OrderDAOImpl odi=new OrderDAOImpl();
		Order order=odi.getOrderByOrderId(id);
		String sql="select ordernumber,partnumber,quantity from orderdetails where ordernumber=?";
		PreparedStatement ps;
		PreparedStatement pspart;
		ResultSet rs;
		ResultSet rspart;
		OrderDetail orderdetail=new OrderDetail();
		List<Product> list=new ArrayList<Product>();
		try{
		ps=conn.prepareStatement(sql);
		ps.setInt(1, id);
		rs=ps.executeQuery();
		
		while(rs.next())
		{
			
			String sqlpart="select * from products where partnumber=?";
			pspart=conn.prepareStatement(sqlpart);
			pspart.setInt(1, rs.getInt("partnumber"));
			rspart=pspart.executeQuery();
			while(rspart.next())
			{   				
				Product p=new Product();			
				p.setColor(rspart.getString("color"));				
				p.setDescription(rspart.getString("description"));				
				p.setDimension(rspart.getString("dimension"));				
				p.setInstock(rspart.getInt("instock"));
				p.setManufacturer(rspart.getString("manufacturer"));
				p.setMinorder(rspart.getInt("minorder"));
				p.setProductnum(rspart.getInt("partnumber"));
				p.setReorderlevel(rspart.getInt("reorderlevel"));
				p.setShelflocation(rspart.getString("shelflocation"));
				p.setUnitprice(rspart.getDouble("unitprice"));
				p.setQuantity(rs.getInt("quantity"));
				p.setProductname(rspart.getString("productname"));
				list.add(p);							
			}
			orderdetail.setEmployeename(order.getEmployeename());
			orderdetail.setOrderdate(order.getOrderdate());
			orderdetail.setOrdernum(rs.getInt("ordernumber"));	
			orderdetail.setQuantity(rs.getInt("quantity"));
			orderdetail.setProduct(list);
				
		}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return orderdetail;
	
		
	}
	public List<OrderDetail> getAllOrderDetail()
	{
		List<OrderDetail> orderdetaillist=new ArrayList<OrderDetail>();
		List<Order> orderList=OrderDAOImpl.getAllOrder();
		for (Order order : orderList) {	
			
			OrderDetail od=this.getOrderDetailByOrderId(order.getOrdernum());
			od.setOrdernum(order.getOrdernum());
			od.setEmployeename(order.getEmployeename());
			od.setOrderdate(order.getOrderdate());
			orderdetaillist.add(od);		
		}	
		return orderdetaillist;		
	}
	
	public void inserOrderDetail(int orderid,int partnum,int quantity)
	{
		String sql="INSERT INTO `orderdetails` (`ordernumber`, `partnumber`, `quantity`) " +
		"VALUES (?, ?, ?)";
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);
		
		ps.setInt(1, orderid);
		ps.setInt(2, partnum);
		ps.setInt(3, quantity);
		ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public List<OrderDetail> getOrderDetailsBtwDate(Date stardate,Date enddate )
	{
		List<OrderDetail> orderdetaillist=new ArrayList<OrderDetail>();
		List<Order> orderList=OrderDAOImpl.getOrderBtwDate(stardate, enddate);
		for (Order order : orderList) {	
			
			OrderDetail od=this.getOrderDetailByOrderId(order.getOrdernum());
			od.setOrdernum(order.getOrdernum());
			od.setEmployeename(order.getEmployeename());
			od.setOrderdate(order.getOrderdate());
			orderdetaillist.add(od);		
		}	
		return orderdetaillist;	
	}

}
